Imports System.Data.SqlClient
Imports System.Data
Imports Ocean.OceanFramework.SQLDataAccess.DataAccessConstants

Namespace SQLDataAccess

    ''' <summary>
    ''' Provides classes to read from and write to the SQL Server database
    ''' </summary>
    Public Class DataAccess

#Region " Declarations "

        Private _enumReturnCode As DatabaseReturnCode = DatabaseReturnCode.NotSet
        Private _intAffectedRows As Integer = -1
        Private _objSQLConnection As SqlConnection
        Private _strConnectionString As String = String.Empty

#End Region

#Region " Properties "

        ''' <summary>
        ''' Gets the number of rows affected by the data operation.
        ''' </summary>
        ''' <returns>Integer</returns>
        Public ReadOnly Property AffectedRows() As Integer
            Get
                Return _intAffectedRows
            End Get
        End Property

        ''' <summary>
        ''' Gets the ReturnCode from the data operation
        ''' </summary>
        ''' <returns>Integer</returns>
        Public ReadOnly Property ReturnCode() As DatabaseReturnCode
            Get
                Return _enumReturnCode
            End Get
        End Property

        ''' <summary>
        ''' Gets the current SQL Connection.
        ''' </summary>
        ''' <returns>SQLConnection</returns>
        Public ReadOnly Property SQLConnection() As SqlConnection
            Get
                Return _objSQLConnection
            End Get
        End Property

#End Region

#Region " Constructors "

        ''' <summary>
        ''' Creates a new instance of the DataAccess class utilizing the connection string passed as a parameter
        ''' </summary>
        ''' <param name="strConnectionString">SQL Client connection string</param>
        Public Sub New(ByVal strConnectionString As String)
            _strConnectionString = strConnectionString
            _objSQLConnection = New SqlConnection(_strConnectionString)
        End Sub

        ''' <summary>
        ''' Creates a new instance of the DataAccess class utilizing the connection passed as a parameter
        ''' </summary>
        ''' <param name="objSQLConnection">SQL Client Connection</param>
        Public Sub New(ByVal objSQLConnection As SqlConnection)
            _objSQLConnection = objSQLConnection
        End Sub

#End Region

#Region " Private Helper Routines "

        Private Function BuildDataAccessException(ByVal enumReturnCode As DatabaseReturnCode, ByVal strSource As String, Optional ByVal strMessage As String = "") As DataAccessException

            If strMessage.Length = 0 Then

                Select Case enumReturnCode

                    Case DatabaseReturnCode.OperationFailed
                        strMessage = "Operation failed.  Please contact tech support."

                    Case DatabaseReturnCode.RecordAlreadyInDatabase
                        strMessage = "Record is already in the database."

                    Case DatabaseReturnCode.RecordNotFound
                        strMessage = "The record was not found."

                    Case DatabaseReturnCode.TimeStampMismatch
                        strMessage = "The record was updated by another user since you selected it.  Please re-edit this record and attempt to save it again."

                    Case Else
                        strMessage = "Value not programmed, please see administrator. Value : " & enumReturnCode.ToString
                End Select

            End If

            Return New DataAccessException(enumReturnCode, strSource, strMessage)
        End Function

        Private Function GetIndexNameFromException(ByVal ex As SqlClient.SqlException) As String

            Dim intUniqueIndexPosition As Integer = ex.Message.ToLower.IndexOf("unique index '")

            If intUniqueIndexPosition > 0 Then

                Dim intEndOfUniqueIndexName As Integer = ex.Message.IndexOf("'", intUniqueIndexPosition + 15)

                If intEndOfUniqueIndexName > 0 Then
                    Return ex.Message.Substring(intUniqueIndexPosition + 14, intEndOfUniqueIndexName - intUniqueIndexPosition - 14)
                End If

            Else
                intUniqueIndexPosition = ex.Message.ToLower.IndexOf("unique key constraint '")

                Dim intEndOfUniqueIndexName As Integer = ex.Message.IndexOf("'", intUniqueIndexPosition + 24)

                If intEndOfUniqueIndexName > 0 Then
                    Return ex.Message.Substring(intUniqueIndexPosition + 23, intEndOfUniqueIndexName - intUniqueIndexPosition - 23)
                End If

            End If

            Return String.Empty
        End Function

        ''' <summary>
        ''' Local helper function to load command parameters into a command object.
        ''' </summary>
        ''' <param name="cmd">Instantiated command object</param>
        ''' <param name="Parameters">Paramarry of sql client parameters</param>
        ''' <remarks>This function was created to allow the loading of the parameters collection to be abstracted, making future modifications easier</remarks>
        Private Sub LoadCommandParameters(ByVal bolLoadReturnValueAndReturnCodeParameter As Boolean, ByRef cmd As SqlCommand, ByVal ParamArray Parameters() As System.Data.SqlClient.SqlParameter)

            If bolLoadReturnValueAndReturnCodeParameter Then
                cmd.Parameters.Add(DataAccessHelper.BuildSQLParameterReturnValue)
            End If

            If Parameters IsNot Nothing Then

                For Each p As SqlParameter In Parameters
                    cmd.Parameters.Add(p)
                Next

            End If

            If bolLoadReturnValueAndReturnCodeParameter Then
                cmd.Parameters.Add(DataAccessHelper.BuildSQLParameterReturnCode)
            End If

        End Sub

        Private Function ParseSQLClientExceptions(ByVal cn As SqlConnection, ByVal ex As Exception, ByRef strDataBaseErrorMessage As String, ByRef enumDatabaseReturnCode As DatabaseReturnCode) As Boolean
            strDataBaseErrorMessage = String.Empty

            If TypeOf ex Is System.Data.SqlClient.SqlException OrElse TypeOf ex.InnerException Is System.Data.SqlClient.SqlException Then

                Dim objSQLException As System.Data.SqlClient.SqlException

                If TypeOf ex Is System.Data.SqlClient.SqlException Then
                    objSQLException = CType(ex, System.Data.SqlClient.SqlException)

                Else
                    objSQLException = CType(ex.InnerException, System.Data.SqlClient.SqlException)
                End If

                If objSQLException.Number = 547 Then

                    enumDatabaseReturnCode = DatabaseReturnCode.ReferentialIntegrityViolated

                    For intError As Integer = 0 To objSQLException.Errors.Count - 1
                        strDataBaseErrorMessage += objSQLException.Errors(intError).Message & vbCrLf
                    Next intError

                    Return True

                ElseIf objSQLException.Number = 2601 OrElse objSQLException.Number = 2627 Then

                    enumDatabaseReturnCode = DatabaseReturnCode.DuplicateKeyViolation

                    Dim strIndexName As String = GetIndexNameFromException(objSQLException)

                    If strIndexName.Length > 0 Then

                        Dim objSQLParam As New System.Data.SqlClient.SqlParameter
                        objSQLParam.SqlDbType = SqlDbType.VarChar
                        objSQLParam.Direction = ParameterDirection.Input
                        objSQLParam.ParameterName = "@INDEXNAME"
                        objSQLParam.Size = 250
                        objSQLParam.Value = strIndexName

                        'save our class instance's current state
                        Dim intSaveAffectedRows As Integer = _intAffectedRows
                        Dim enumSaveReturnCode As DatabaseReturnCode = DatabaseReturnCode.DuplicateKeyViolation

                        'Good reuse of an open sql connection object
                        Dim objIndexFields As Object = Me.ExecuteScaler(System.Data.CommandType.StoredProcedure, "SystemGetIndexColumnNames", objSQLParam)
                        'restore our class instance's current state
                        _intAffectedRows = intSaveAffectedRows
                        _enumReturnCode = DatabaseReturnCode.DuplicateKeyViolation

                        If objIndexFields IsNot Nothing Then
                            strDataBaseErrorMessage = String.Format("While saving this record there was a violation of a database unique index.  The follow field(s) make up the unique index {0}.  Please see your administrator for data entry help.", Convert.ToString(objIndexFields))
                            Return True

                        Else
                            strDataBaseErrorMessage = "While saving this record there was a violation of a database unique index. The system was unable to retrieve the column names. Detailed error message is below.  Please see your administrator for data entry help."

                            For intError As Integer = 0 To objSQLException.Errors.Count - 1
                                strDataBaseErrorMessage += objSQLException.Errors(intError).Message & vbCrLf
                            Next intError

                            Return True
                        End If

                    Else
                        strDataBaseErrorMessage = "While saving this record there was a violation of a database unique index. The system was unable to retrieve the column names. Detailed error message is below.  Please see your administrator for data entry help."

                        For intError As Integer = 0 To objSQLException.Errors.Count - 1
                            strDataBaseErrorMessage += objSQLException.Errors(intError).Message & vbCrLf
                        Next intError

                        Return True
                    End If

                End If

            Else
                Return False
            End If

        End Function

        ''' <summary>
        ''' Local helper function to load the class properties that are loaded after every data operation
        ''' </summary>
        ''' <param name="cmdParameters">Command paramertes collection</param>
        Private Sub SetClassPropertiesFromOutboundParameters(ByVal cmdParameters As SqlParameterCollection)
            _intAffectedRows = CType(cmdParameters.Item(G_STRING_RETURN_VALUE).Value, Integer)
            _enumReturnCode = CType(cmdParameters.Item(G_STRING_RETURN_CODE).Value, DatabaseReturnCode)
        End Sub

#End Region

#Region " Execute Scalar "

        ''' <summary>
        ''' Executes a sql command using the supplied parameters and returns a single value.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <returns>A single value</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader.</remarks>
        Public Function ExecuteScaler(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String) As Object
            Return ExecuteScaler(CommandType, CommandText, 0, Nothing)
        End Function

        ''' <summary>
        ''' Executes a sql command using the supplied parameters and returns a single value.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>A single value</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader.</remarks>
        Public Function ExecuteScaler(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As Object
            Return ExecuteScaler(CommandType, CommandText, 0, Parameters)
        End Function

        ''' <summary>
        ''' Executes a sql command using the supplied parameters and returns a single value.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandTimeOut">Time out value for this request</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>A single value</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader</remarks>
        Private Function ExecuteScaler(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandTimeOut As Integer, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As Object

            Dim bolConnectionWasOpenOnCall As Boolean = False

            If Me.SQLConnection.State = ConnectionState.Open Then
                bolConnectionWasOpenOnCall = True

            ElseIf Me.SQLConnection.State = ConnectionState.Closed Then
                bolConnectionWasOpenOnCall = False

            Else
                Throw New DataAccessException(DatabaseReturnCode.ConnectionStateError, "ExecuteScaler", String.Format("The sqlconnection state was {0}.  The method only permits the sqlconnection state to be either open or closed only.", Me.SQLConnection.State.ToString))
            End If

            If String.IsNullOrEmpty(CommandText) Then
                Throw New DataAccessException(DatabaseReturnCode.MissingCommandText, "ExecuteScaler", "The method did not receive any command text.  Calling code must supply the command text.")
            End If

            Dim cmd As New SqlCommand()

            If Me.SQLConnection.State = ConnectionState.Open Then
                'connection is being controlled by the calling procedure
                Using cmd
                    cmd.CommandText = CommandText
                    cmd.CommandType = CommandType
                    cmd.CommandTimeout = CommandTimeOut
                    cmd.Connection = Me.SQLConnection

                    If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                        LoadCommandParameters(True, cmd, Parameters)
                    End If

                    Dim objReturnObject As Object = cmd.ExecuteScalar()

                    If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                        SetClassPropertiesFromOutboundParameters(cmd.Parameters)

                        If Me.ReturnCode <> DatabaseReturnCode.Successful Then
                            Throw BuildDataAccessException(Me.ReturnCode, "ExecuteScaler", "")
                        End If

                    End If

                    Return objReturnObject
                End Using

            Else
                'connection will be controlled by this procedure, ie. connection state is closed
                Using Me.SQLConnection
                    Using cmd
                        cmd.CommandText = CommandText
                        cmd.CommandType = CommandType
                        cmd.CommandTimeout = CommandTimeOut
                        cmd.Connection = Me.SQLConnection

                        If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                            LoadCommandParameters(True, cmd, Parameters)
                        End If

                        Me.SQLConnection.Open()

                        Dim objReturnObject As Object = cmd.ExecuteScalar()

                        If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                            SetClassPropertiesFromOutboundParameters(cmd.Parameters)

                            If Me.ReturnCode <> DatabaseReturnCode.Successful Then
                                Throw BuildDataAccessException(Me.ReturnCode, "ExecuteScaler", "")
                            End If

                        End If

                        Return objReturnObject
                    End Using
                End Using
            End If

        End Function

#End Region

#Region " Execute Non Query "

        ''' <summary>
        ''' Executes a sql command using the supplied parameters and returns the number of rows affected
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <returns>The number of rows affected</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>
        ''' <para>You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements</para>
        ''' <para>Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data</para> 
        ''' <para>For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1</para> 
        ''' </remarks>
        Public Function ExecuteNonQuery(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String) As Integer
            Return ExecuteNonQuery(CommandType, CommandText, 0, Nothing)
        End Function

        ''' <summary>
        ''' Executes a sql command using the supplied parameters and returns the number of rows affected
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandTimeOut">Time out value for this request</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>The number of rows affected</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>
        ''' <para>You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements</para>
        ''' <para>Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data</para> 
        ''' <para>For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1</para> 
        ''' </remarks>
        Public Function ExecuteNonQuery(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandTimeOut As Integer, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As Integer

            Dim bolConnectionWasOpenOnCall As Boolean = False

            If Me.SQLConnection.State = ConnectionState.Open Then
                bolConnectionWasOpenOnCall = True

            ElseIf Me.SQLConnection.State = ConnectionState.Closed Then
                bolConnectionWasOpenOnCall = False

            Else
                Throw New DataAccessException(DatabaseReturnCode.ConnectionStateError, "ExecuteNonQuery", String.Format("The sqlconnection state was {0}.  The method only permits the sqlconnection state to be either open or closed only.", Me.SQLConnection.State.ToString))
            End If

            If String.IsNullOrEmpty(CommandText) Then
                Throw New DataAccessException(DatabaseReturnCode.MissingCommandText, "ExecuteNonQuery", "The method did not receive any command text.  Calling code must supply the command text.")
            End If

            Dim cmd As New SqlCommand()

            If Me.SQLConnection.State = ConnectionState.Open Then
                'connection is being controlled by the calling procedure
                Using cmd
                    cmd.CommandText = CommandText
                    cmd.CommandType = CommandType
                    cmd.CommandTimeout = CommandTimeOut
                    cmd.Connection = Me.SQLConnection

                    If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                        LoadCommandParameters(True, cmd, Parameters)
                    End If

                    Try
                        _intAffectedRows = cmd.ExecuteNonQuery()

                        If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                            SetClassPropertiesFromOutboundParameters(cmd.Parameters)

                            If Me.ReturnCode <> DatabaseReturnCode.Successful Then
                                Throw BuildDataAccessException(Me.ReturnCode, "ExecuteNonQuery", "")
                            End If

                        End If

                    Catch ex As Exception

                        'must catch all exceptions, some we'll just let go, others need to be processed
                        'but because exceptions now have wrappers we have catch and parse.  Thank you Microsoft :-(((
                        'in other words we can't catch the SQL Exception directly Thank you Microsoft :-(((
                        Dim strDataBaseErrorMessage As String = String.Empty

                        If ParseSQLClientExceptions(Me.SQLConnection, ex, strDataBaseErrorMessage, _enumReturnCode) = True Then
                            Throw BuildDataAccessException(_enumReturnCode, "ExecuteNonQuery", strDataBaseErrorMessage)

                        Else
                            Throw ex
                        End If

                    End Try

                    Return Me.AffectedRows
                End Using

            Else
                'connection will be controlled by this procedure, ie. connection state is closed
                Using Me.SQLConnection
                    Using cmd
                        cmd.CommandText = CommandText
                        cmd.CommandType = CommandType
                        cmd.CommandTimeout = CommandTimeOut
                        cmd.Connection = Me.SQLConnection

                        If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                            LoadCommandParameters(True, cmd, Parameters)
                        End If

                        Me.SQLConnection.Open()

                        Try
                            _intAffectedRows = cmd.ExecuteNonQuery()

                            If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                                SetClassPropertiesFromOutboundParameters(cmd.Parameters)

                                If Me.ReturnCode <> DatabaseReturnCode.Successful Then
                                    Throw BuildDataAccessException(Me.ReturnCode, "ExecuteNonQuery", "")
                                End If

                            End If

                        Catch ex As Exception

                            'must catch all exceptions, some we'll just let go, others need to be processed
                            'but because exceptions now have wrappers we have catch and parse.  
                            'in other words we can't catch the SQL Exception directly Thank you Microsoft :-(((
                            Dim strDataBaseErrorMessage As String = String.Empty

                            If ParseSQLClientExceptions(Me.SQLConnection, ex, strDataBaseErrorMessage, _enumReturnCode) = True Then
                                Throw BuildDataAccessException(_enumReturnCode, "ExecuteNonQuery", strDataBaseErrorMessage)

                            Else
                                Throw ex
                            End If

                        End Try

                        Return Me.AffectedRows
                    End Using
                End Using
            End If

        End Function

        ''' <summary>
        ''' Executes a sql command using the supplied parameters and returns the number of rows affected
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>The number of rows affected</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>
        ''' <para>You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements</para>
        ''' <para>Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data</para> 
        ''' <para>For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1</para> 
        ''' </remarks>
        Public Function ExecuteNonQuery(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As Integer
            Return ExecuteNonQuery(CommandType, CommandText, 0, Parameters)
        End Function

#End Region

#Region " Dataset "

        ''' <summary>
        ''' Fills a dataset using the supplied parameters and table name(s).
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandTimeOut">Time out value for this request</param>
        ''' <param name="TableNames">String array of table names that will be assigned to the returned dataset</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>Dataset object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="TableNames" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataSet(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandTimeOut As Integer, ByVal TableNames() As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As DataSet
            Return GetDataSet(CommandType, CommandText, CommandTimeOut, New DataSet, TableNames, Parameters)
        End Function

        ''' <summary>
        ''' Fills a dataset using the supplied parameters and table name(s).
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandTimeOut">Time out value for this request</param>
        ''' <param name="ds">Instianted dataset.  This can be a Typed dataset or an un-typed dataset</param>
        ''' <param name="TableNames">String array of table names that will be assigned to the returned dataset</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>Dataset object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="TableNames" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="ds" /><c>Is Nothing</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataSet(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandTimeOut As Integer, ByVal ds As DataSet, ByVal TableNames() As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As DataSet

            Dim bolConnectionWasOpenOnCall As Boolean = False

            If Me.SQLConnection.State = ConnectionState.Open Then
                bolConnectionWasOpenOnCall = True

            ElseIf Me.SQLConnection.State = ConnectionState.Closed Then
                bolConnectionWasOpenOnCall = False

            Else
                Throw New DataAccessException(DatabaseReturnCode.ConnectionStateError, "GetDataSet", String.Format("The sqlconnection state was {0}.  The method only permits the sqlconnection state to be either open or closed only.", Me.SQLConnection.State.ToString))
            End If

            If TableNames Is Nothing OrElse TableNames.Length = 0 Then
                Throw New DataAccessException(DatabaseReturnCode.MissingTableName, "GetDataSet", "The method did not receive any TableNames.  At least one table name must be supplied.")
            End If

            If String.IsNullOrEmpty(CommandText) Then
                Throw New DataAccessException(DatabaseReturnCode.MissingCommandText, "GetDataSet", "The method did not receive any command text.  Calling code must supply the command text.")
            End If

            If ds Is Nothing Then
                Throw New DataAccessException(DatabaseReturnCode.MissingDataSetInstance, "GetDataSet", "The method did not receive an instianted dataset, dataset was nothing.  The dataset must be instianted before calling this procedure or else use one of the other overloads for returning a dataset that does instiante the dataset for the calling code.")
            End If

            Dim cmd As New SqlCommand()

            If Me.SQLConnection.State = ConnectionState.Open Then
                'connection is being controlled by the calling procedure
                Using cmd
                    cmd.CommandText = CommandText
                    cmd.CommandType = CommandType
                    cmd.CommandTimeout = CommandTimeOut
                    cmd.Connection = Me.SQLConnection

                    If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                        LoadCommandParameters(False, cmd, Parameters)
                    End If

                    Dim da As New SqlDataAdapter(cmd)

                    If cmd.CommandType = System.Data.CommandType.StoredProcedure Then

                        Dim intX As Integer = 0

                        For Each s As String In TableNames

                            If intX = 0 Then
                                da.TableMappings.Add("Table", s)

                            Else
                                da.TableMappings.Add("Table" & intX.ToString, s)
                            End If

                            intX += 1
                        Next

                    End If

                    da.Fill(ds)
                End Using

            Else
                'connection will be controlled by this procedure, ie. connection state is closed
                Using Me.SQLConnection
                    Using cmd
                        cmd.CommandText = CommandText
                        cmd.CommandType = CommandType
                        cmd.CommandTimeout = CommandTimeOut
                        cmd.Connection = Me.SQLConnection

                        If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                            LoadCommandParameters(False, cmd, Parameters)
                        End If

                        Dim da As New SqlDataAdapter(cmd)

                        If cmd.CommandType = System.Data.CommandType.StoredProcedure Then

                            Dim intX As Integer = 0

                            For Each s As String In TableNames

                                If intX = 0 Then
                                    da.TableMappings.Add("Table", s)

                                Else
                                    da.TableMappings.Add("Table" & intX.ToString, s)
                                End If

                                intX += 1
                            Next

                        End If

                        Me.SQLConnection.Open()
                        da.Fill(ds)
                    End Using
                End Using
            End If

            Return ds
        End Function

        ''' <summary>
        ''' Fills a dataset using the supplied parameters and table name(s).
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="TableNames">String array of table names that will be assigned to the returned dataset</param>
        ''' <returns>Dataset object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="TableNames" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataSet(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal TableNames() As String) As DataSet
            Return GetDataSet(CommandType, CommandText, 0, New DataSet, TableNames, Nothing)
        End Function

        ''' <summary>
        ''' Fills a dataset using the supplied parameters and table name(s).
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="TableNames">String array of table names that will be assigned to the returned dataset</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>Dataset object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="TableNames" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataSet(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal TableNames() As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As DataSet
            Return GetDataSet(CommandType, CommandText, 0, New DataSet, TableNames, Parameters)
        End Function

        ''' <summary>
        ''' Fills a dataset using the supplied parameters and table name(s).
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="ds">Instianted dataset.  This can be a Typed dataset or an un-typed dataset</param>
        ''' <param name="TableNames">String array of table names that will be assigned to the returned dataset</param>
        ''' <param name="Parameters">This is a param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>Dataset object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="TableNames" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="ds" /><c>Is Nothing</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataSet(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal ds As DataSet, ByVal TableNames() As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As DataSet
            Return GetDataSet(CommandType, CommandText, 0, ds, TableNames, Parameters)
        End Function

#End Region

#Region " DataReader "

        ''' <summary>
        ''' Executes a sql command to return a SQL Client DataReader object.  The developer MUST close the DataReader and Connection object in the calling code when done with the reader.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandBehavior">Command behaviors should be selected to get best performance.  The close connection behavior should be selected to ensure that the connection is closed when the DataReader is closed</param>
        ''' <returns>SQL DataReader object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        Public Function GetDataReader(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandBehavior As System.Data.CommandBehavior) As SqlDataReader
            Return GetDataReader(CommandType, CommandText, CommandBehavior, 0, Nothing)
        End Function

        ''' <summary>
        ''' Executes a sql command to return a SQL Client DataReader object.  The developer MUST close the DataReader and Connection object in the calling code when done with the reader.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandBehavior">Command behaviors should be selected to get best performance.  The close connection behavior should be selected to ensure that the connection is closed when the DataReader is closed</param>
        ''' <param name="CommandTimeOut">Time out value for this request</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>SQL DataReader object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        Public Function GetDataReader(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandBehavior As System.Data.CommandBehavior, ByVal CommandTimeOut As Integer, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As SqlDataReader

            Dim bolConnectionWasOpenOnCall As Boolean = False

            If Me.SQLConnection.State = ConnectionState.Open Then
                bolConnectionWasOpenOnCall = True

            ElseIf Me.SQLConnection.State = ConnectionState.Closed Then
                bolConnectionWasOpenOnCall = False

            Else
                Throw New DataAccessException(DatabaseReturnCode.ConnectionStateError, "GetDataReader", String.Format("The sqlconnection state was {0}.  The method only permits the sqlconnection state to be either open or closed only.", Me.SQLConnection.State.ToString))
            End If

            If String.IsNullOrEmpty(CommandText) Then
                Throw New DataAccessException(DatabaseReturnCode.MissingCommandText, "GetDataReader", "The method did not receive any command text.  Calling code must supply the command text.")
            End If

            Dim cmd As New System.Data.SqlClient.SqlCommand()
            cmd.CommandText = CommandText
            cmd.CommandType = CommandType
            cmd.CommandTimeout = CommandTimeOut
            cmd.Connection = Me.SQLConnection

            If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                LoadCommandParameters(False, cmd, Parameters)
            End If

            If Me.SQLConnection.State = ConnectionState.Closed Then
                Me.SQLConnection.Open()
            End If

            Return cmd.ExecuteReader(CommandBehavior)
        End Function

        ''' <summary>
        ''' Executes a sql command to return a SQL Client DataReader object.  The developer MUST close the DataReader and Connection object in the calling code when done with the reader.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandBehavior">Command behaviors should be selected to get best performance.  The close connection behavior should be selected to ensure that the connection is closed when the DataReader is closed</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>SQL DataReader object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        Public Function GetDataReader(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandBehavior As System.Data.CommandBehavior, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As SqlDataReader
            Return GetDataReader(CommandType, CommandText, CommandBehavior, 0, Parameters)
        End Function

#End Region

#Region " DataTable "

        ''' <summary>
        ''' Fills a datatable using the supplied parameters and table name.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandTimeOut">Time out value for this request</param>
        ''' <param name="TableName">String table name that will be assigned to the returned datatable</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>Datatable object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="TableName" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataTable(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandTimeOut As Integer, ByVal TableName As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As DataTable
            Return GetDataTable(CommandType, CommandText, CommandTimeOut, New DataTable, TableName, Parameters)
        End Function

        ''' <summary>
        ''' Fills a datatable using the supplied parameters and table name.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandTimeOut">Time out value for this request</param>
        ''' <param name="dt">Instianted datatable</param>
        ''' <param name="TableName">String table name that will be assigned to the returned datatable</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>Dataset object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="TableName" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="dt" /><c>Is Nothing</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataTable(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandTimeOut As Integer, ByVal dt As DataTable, ByVal TableName As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As DataTable

            Dim bolConnectionWasOpenOnCall As Boolean = False
            dt.TableName = TableName

            If Me.SQLConnection.State = ConnectionState.Open Then
                bolConnectionWasOpenOnCall = True

            ElseIf Me.SQLConnection.State = ConnectionState.Closed Then
                bolConnectionWasOpenOnCall = False

            Else
                Throw New DataAccessException(DatabaseReturnCode.ConnectionStateError, "GetDataTable", String.Format("The sqlconnection state was {0}.  The method only permits the sqlconnection state to be either open or closed only.", Me.SQLConnection.State.ToString))
            End If

            If TableName Is Nothing OrElse TableName.Length = 0 Then
                Throw New DataAccessException(DatabaseReturnCode.MissingTableName, "GetDataTable", "The method did not receive the TableName.")
            End If

            If String.IsNullOrEmpty(CommandText) Then
                Throw New DataAccessException(DatabaseReturnCode.MissingCommandText, "GetDataTable", "The method did not receive any command text.  Calling code must supply the command text.")
            End If

            If dt Is Nothing Then
                Throw New DataAccessException(DatabaseReturnCode.MissingDataSetInstance, "GetDataTable", "The method did not receive an instianted dataset, datatable was nothing.  The datatable must be instianted before calling this procedure or else use one of the other overloads for returning a datatable that does instiante the datatable for the calling code.")
            End If

            Dim cmd As New SqlCommand()

            If Me.SQLConnection.State = ConnectionState.Open Then
                'connection is being controlled by the calling procedure
                Using cmd
                    cmd.CommandText = CommandText
                    cmd.CommandType = CommandType
                    cmd.CommandTimeout = CommandTimeOut
                    cmd.Connection = Me.SQLConnection

                    If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                        LoadCommandParameters(False, cmd, Parameters)
                    End If

                    Dim da As New SqlDataAdapter(cmd)

                    If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                        da.TableMappings.Add("Table", TableName)
                    End If

                    da.Fill(dt)
                End Using

            Else
                'connection will be controlled by this procedure, ie. connection state is closed
                Using Me.SQLConnection
                    Using cmd
                        cmd.CommandText = CommandText
                        cmd.CommandType = CommandType
                        cmd.CommandTimeout = CommandTimeOut
                        cmd.Connection = Me.SQLConnection

                        If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                            LoadCommandParameters(False, cmd, Parameters)
                        End If

                        Dim da As New SqlDataAdapter(cmd)

                        If cmd.CommandType = System.Data.CommandType.StoredProcedure Then
                            da.TableMappings.Add("Table", TableName)
                        End If

                        Me.SQLConnection.Open()
                        da.Fill(dt)
                    End Using
                End Using
            End If

            Return dt
        End Function

        ''' <summary>
        ''' Fills a datatable using the supplied parameters and table name.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="TableName">String table name that will be assigned to the returned datatable</param>
        ''' <returns>Datatable object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="TableName" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataTable(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal TableName As String) As DataTable
            Return GetDataTable(CommandType, CommandText, 0, New DataTable, TableName, Nothing)
        End Function

        ''' <summary>
        ''' Fills a datatable using the supplied parameters and table name.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="TableName">String table name that will be assigned to the returned datatable</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>Datatable object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="TableName" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataTable(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal TableName As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As DataTable
            Return GetDataTable(CommandType, CommandText, 0, New DataTable, TableName, Parameters)
        End Function

        ''' <summary>
        ''' Fills a datatable using the supplied parameters and table name.
        ''' </summary>
        ''' <param name="CommandType">SQL command type, normally this is stored procedure</param>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="dt">Instianted datatable</param>
        ''' <param name="TableName">String table name that will be assigned to the returned datatable</param>
        ''' <param name="Parameters">This is a param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>Datatable object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="TableName" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' <para>Thrown when <paramref name="dt" /><c>Is Nothing</c></para>
        ''' </exception>
        ''' <remarks>See the SQLDataAdapter class help documentation for a complete explaination of the Fill method.</remarks>
        Public Function GetDataTable(ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal dt As DataTable, ByVal TableName As String, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As DataTable
            Return GetDataTable(CommandType, CommandText, 0, dt, TableName, Parameters)
        End Function

#End Region

    End Class

End Namespace
